﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using TraditionalMalteseCuisine.App_Code.Operations;

namespace TraditionalMalteseCuisine.App_Code.Objects
{
    [Serializable]
    public class Recipe : BaseTableWithMediaFields
    {
        //*************************************************
        //                Class Members
        //*************************************************
        #region Class Members
        public const string DEFAULT_IMAGE_LOCATION = DEFAULT_IMAGE_FOLDER_LOCATION + "default_recipe.png";
        public const string DEFAULT_THUMBNAIL_LOCATION = DEFAULT_THUMBNAIL_FOLDER_LOCATION + "default_recipe.png";

        public const string TABLE_NAME = "Recipes";
        public const string VIEW_NAME_ALL_RECIPES = "vwAll" + TABLE_NAME;
        public const string VIEW_NAME_ALL_RECIPES_DETAILS = VIEW_NAME_ALL_RECIPES + "Details";

        public const string MEMBER_ID_TAG = "member_" + SiteMember.ID_TAG;
        public const string LEVEL_ID_TAG = "level_" + Level.ID_TAG;
        public const string RECIPE_NAME_TAG = "recipe_name";
        public const string POSTED_DATE_TAG = "posted_date";
        public const string INGREDIENTS_TAG = "ingredients";
        public const string DESCRIPTION_TAG = "description";
        public const string DIRECTIONS_TAG = "directions";

        private int mMemberID = -1;
        private int mLevelID = -1;
        private string mRecipeName = string.Empty;
        private DateTime mPostedDate = DateTime.MinValue;
        private string mIngredients = string.Empty;
        private string mDescription = string.Empty;
        private string mDirections = string.Empty;

        private List<RecipeTag> mRecipesTags = new List<RecipeTag>();
        #endregion
        //*************************************************


        //*************************************************
        //                   Constructor
        //*************************************************
        #region Constructor
        public Recipe()
        {
            ImageLocation = DEFAULT_IMAGE_LOCATION;
            ThumbnailLocation = DEFAULT_THUMBNAIL_LOCATION;
        }
        #endregion
        //*************************************************


        //*************************************************
        //               Static Methods
        //*************************************************
        #region Static Methods
        public static OleDbCommand GetCommand_SearchByRecipeName(string value)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT * FROM {0} WHERE (", VIEW_NAME_ALL_RECIPES);

            commandText.AppendFormat("([{0}] LIKE '%' + @{0} + '%') ", RECIPE_NAME_TAG);
            oledbCommand.Parameters.AddWithValue(string.Format("@{0}", RECIPE_NAME_TAG), value);

            commandText.Append(")");

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }

        public static OleDbCommand GetCommand_GetTopMostRecentRecipes(int amount)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT TOP {0} * FROM {1} ORDER BY {2} DESC, {3} ASC", amount, VIEW_NAME_ALL_RECIPES, POSTED_DATE_TAG, Recipe.RECIPE_NAME_TAG);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }

        public static OleDbCommand GetCommand_GetTopMostLikedRecipes(int amount)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT TOP {0} * FROM {1} ORDER BY {2} DESC, {3} ASC", amount, RecipeLike.VIEW_NAME_RECIPES_LIKES_TOTAL_LIKES, RecipeLike.TOTAL_LIKES_TAG, Recipe.RECIPE_NAME_TAG);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }

        public static OleDbCommand GetCommand_GetTopMostRatedRecipes(int amount)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT TOP {0} * FROM {1} ORDER BY {2} DESC, {3} DESC, {4} ASC", amount, RecipeRating.VIEW_NAME_ALL_RECIPES_RATINGS_AVERAGES, RecipeRating.TOTAL_VOTES_TAG, RecipeRating.AVERAGE_RATING_TAG, Recipe.RECIPE_NAME_TAG);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }

        public static OleDbCommand GetCommand_GetTopMostLikedRecipes(int amount, int memberID)
        {
            OleDbCommand oledbCommand = new OleDbCommand();
            oledbCommand.CommandType = CommandType.Text;

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat("SELECT TOP {0} * FROM {1} WHERE ({2} = {3}) ORDER BY {4} DESC, {5} ASC", amount, RecipeLike.VIEW_NAME_RECIPES_LIKES_TOTAL_LIKES, MEMBER_ID_TAG, memberID, RecipeLike.TOTAL_LIKES_TAG, Recipe.RECIPE_NAME_TAG);

            oledbCommand.CommandText = commandText.ToString();

            return oledbCommand;
        }
        #endregion
        //*************************************************


        //*************************************************
        //                   Methods
        //*************************************************
        #region Methods
        protected override string GetInsertScriptUsingParameters()
        {
            StringBuilder insertScript = new StringBuilder();
            
            insertScript.Append("INSERT INTO ");
            insertScript.AppendFormat("{0} ", TABLE_NAME);
            insertScript.AppendFormat("([{0}], [{1}], [{2}], [{3}], [{4}], [{5}], [{6}], [{7}]) ", MEMBER_ID_TAG, LEVEL_ID_TAG, THUMBNAIL_LOCATION_TAG, IMAGE_LOCATION_TAG, RECIPE_NAME_TAG, INGREDIENTS_TAG, DESCRIPTION_TAG, DIRECTIONS_TAG);
            insertScript.Append("VALUES ");
            insertScript.AppendFormat("(@{0}, @{1}, @{2}, @{3}, @{4}, @{5}, @{6}, @{7}) ", MEMBER_ID_TAG, LEVEL_ID_TAG, THUMBNAIL_LOCATION_TAG, IMAGE_LOCATION_TAG, RECIPE_NAME_TAG, INGREDIENTS_TAG, DESCRIPTION_TAG, DIRECTIONS_TAG);

            return insertScript.ToString();
        }

        public override OleDbCommand GetInsertCommand()
        {
            OleDbCommand insertCommand = new OleDbCommand();

            insertCommand.CommandType = CommandType.Text;
            insertCommand.CommandText = GetInsertScriptUsingParameters();

            insertCommand.Parameters.AddWithValue(string.Format("@{0}", MEMBER_ID_TAG), MemberID);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", LEVEL_ID_TAG), LevelID);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", THUMBNAIL_LOCATION_TAG), ThumbnailLocation);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", IMAGE_LOCATION_TAG), ImageLocation);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", RECIPE_NAME_TAG), RecipeName);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", INGREDIENTS_TAG), Ingredients);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", DESCRIPTION_TAG), Description);
            insertCommand.Parameters.AddWithValue(string.Format("@{0}", DIRECTIONS_TAG), Directions);

            return insertCommand;
        }

        public override BaseTable ReadDataRow(DataRow datarow)
        {
            ID = int.Parse(datarow[ID_TAG].ToString());
            MemberID = int.Parse(datarow[MEMBER_ID_TAG].ToString());
            LevelID = int.Parse(datarow[LEVEL_ID_TAG].ToString());
            ThumbnailLocation = datarow[THUMBNAIL_LOCATION_TAG].ToString();
            ImageLocation = datarow[IMAGE_LOCATION_TAG].ToString();
            RecipeName = datarow[RECIPE_NAME_TAG].ToString();
            PostedDate = DateTime.Parse(datarow[POSTED_DATE_TAG].ToString());
            Ingredients = datarow[INGREDIENTS_TAG].ToString();
            Description = datarow[DESCRIPTION_TAG].ToString();
            Directions = datarow[DIRECTIONS_TAG].ToString();
            InUse = bool.Parse(datarow[IN_USE_TAG].ToString());

            DataTable dtRecipeTags = ReadOp.GetDataTable(RecipeTag.GetCommand_GetRecordsBy(RecipeTag.TABLE_NAME, RecipeTag.RECIPE_ID_TAG, ID.ToString()));

            if (dtRecipeTags != null)
                foreach (DataRow dr in dtRecipeTags.Rows)
                    RecipesTags.Add((RecipeTag)(new RecipeTag()).ReadDataRow(dr));

            return this;
        }


        public override string ToString()
        {
            return string.Format("{0}", RecipeName);
        }
        #endregion
        //*************************************************


        //*************************************************
        //                  Properties
        //*************************************************
        #region Properties
        public int MemberID
        {
            get { return mMemberID; }
            set { mMemberID = value; }
        }

        public int LevelID
        {
            get { return mLevelID; }
            set { mLevelID = value; }
        }

        public string RecipeName
        {
            get { return mRecipeName; }
            set { mRecipeName = value; }
        }

        public DateTime PostedDate
        {
            get { return mPostedDate; }
            set { mPostedDate = value; }
        }

        public string Ingredients
        {
            get { return mIngredients; }
            set { mIngredients = value; }
        }

        public string Description
        {
            get { return mDescription; }
            set { mDescription = value; }
        }

        public string Directions
        {
            get { return mDirections; }
            set { mDirections = value; }
        }

        public List<RecipeTag> RecipesTags
        {
            get { return mRecipesTags; }
            set { mRecipesTags = value; }
        }
        #endregion
        //*************************************************
    }
}